Many commercial databases and applications store their data in files. A database may be divided into one or more logical storage units called table spaces, and a table space may contain logical entities, such as tables and indexes. A table space may be stored in one or more physical data files. Thus, a database may store data logically in table spaces and physically in data files associated with a corresponding table space. A data file is associated with only one table space and only one database.
Table spaces may further be divided into logical units referred to as segments, which may be divided into extents. Extents are a collection of contiguous blocks in a data file. For tables, storage space may be allocated on demand as new rows are inserted into a table. Tables may be spread across one or more data files by allocating extents from different data files each time rows are inserted into a table. Thus, a data file may include extents of multiple tables, and a table may include extents from multiple data files.
The data files are typically large in size, and portions of the files are randomly accessed. Accordingly, data stored in some portions of a data file may be subsequently accessed either infrequently or not at all, remaining relatively untouched or cold for extended periods of time or over the remaining life of the data file.
Conventional storage management and file relocation solutions use multi-tier storage systems to balance performance and costs. At higher tiers, performance is better but the cost is higher, while at lower tiers the cost is reduced but so is performance. It is common practice to move older files, which are usually accessed less frequently than newer files, to a lower tier in a multi-tier storage system in order to reduce costs. As a result, extents belonging to multiple tables may be relocated to a lower tier, even if some of those extents are accessed frequently.
More specifically, each block in a table extent usually includes multiple table rows. When an existing row is deleted, the space occupied by the deleted row is marked as free space and a new row may then be inserted into the free space. If a table is subject to frequent deletes and inserts, then blocks in that table will likely include newer rows of data interleaved with older rows of data. Thus, when an entire data file is moved to a lower tier, both newer rows and older rows will be moved to the lower tier, which can degrade performance whenever the newer rows need to be accessed. Even the movement of a single block to a lower tier can adversely impact performance if that block contains both newer rows and older rows.